"""
定时任务服务类
提供任务的CRUD操作和调度管理
"""
from typing import Optional, List, Dict, Any
from sqlalchemy.orm import Session
from sqlalchemy import and_, or_, desc, func, text
from datetime import datetime, date, timedelta
from uuid import UUID

from app.models.scheduled_job import ScheduledJob, JobLog, JobGroup, JobStatus, JobType
from app.schemas.scheduled_job import (
    JobCreate, JobUpdate, JobLogCreate, JobLogUpdate, 
    JobGroupCreate, JobGroupUpdate, ExecutionStatus, TriggerType
)


class ScheduledJobService:
    """定时任务服务"""
    
    def __init__(self):
        pass
    
    # ==================== 任务CRUD操作 ====================
    
    def create_job(self, db: Session, job_data: JobCreate, created_by: Optional[UUID] = None) -> ScheduledJob:
        """创建定时任务"""
        job_dict = job_data.model_dump(exclude_unset=True)
        job_dict['created_by'] = created_by
        
        db_job = ScheduledJob(**job_dict)
        db.add(db_job)
        db.commit()
        db.refresh(db_job)
        return db_job
    
    def get_job(self, db: Session, job_id: UUID) -> Optional[ScheduledJob]:
        """获取单个任务"""
        return db.query(ScheduledJob).filter(ScheduledJob.id == job_id).first()
    
    def get_job_by_name(self, db: Session, job_name: str) -> Optional[ScheduledJob]:
        """根据任务名称获取任务"""
        return db.query(ScheduledJob).filter(ScheduledJob.job_name == job_name).first()
    
    def get_jobs(
        self, 
        db: Session, 
        skip: int = 0, 
        limit: int = 100,
        job_group: Optional[str] = None,
        job_status: Optional[str] = None,
        job_type: Optional[str] = None,
        search: Optional[str] = None
    ) -> tuple[List[ScheduledJob], int]:
        """获取任务列表"""
        query = db.query(ScheduledJob)
        
        # 添加过滤条件
        if job_group:
            query = query.filter(ScheduledJob.job_group == job_group)
        if job_status:
            query = query.filter(ScheduledJob.job_status == job_status)
        if job_type:
            query = query.filter(ScheduledJob.job_type == job_type)
        if search:
            query = query.filter(
                or_(
                    ScheduledJob.job_name.ilike(f"%{search}%"),
                    ScheduledJob.description.ilike(f"%{search}%"),
                    ScheduledJob.executor_handler.ilike(f"%{search}%")
                )
            )
        
        # 获取总数
        total = query.count()
        
        # 分页查询
        jobs = query.order_by(desc(ScheduledJob.created_at)).offset(skip).limit(limit).all()
        
        return jobs, total
    
    def update_job(
        self, 
        db: Session, 
        job_id: UUID, 
        job_data: JobUpdate, 
        updated_by: Optional[UUID] = None
    ) -> Optional[ScheduledJob]:
        """更新任务"""
        job = self.get_job(db, job_id)
        if not job:
            return None
        
        update_dict = job_data.model_dump(exclude_unset=True)
        update_dict['updated_by'] = updated_by
        update_dict['updated_at'] = datetime.now()
        
        for field, value in update_dict.items():
            setattr(job, field, value)
        
        db.commit()
        db.refresh(job)
        return job
    
    def delete_job(self, db: Session, job_id: UUID) -> bool:
        """删除任务"""
        job = self.get_job(db, job_id)
        if not job:
            return False
        
        db.delete(job)
        db.commit()
        return True
    
    def update_job_status(self, db: Session, job_id: UUID, status: "JobStatus") -> Optional[ScheduledJob]:
        """更新任务状态"""
        job = self.get_job(db, job_id)
        if not job:
            return None
        
        job.job_status = str(status)  # type: ignore
        job.updated_at = datetime.now()  # type: ignore
        
        db.commit()
        db.refresh(job)
        return job
    
    # ==================== 任务执行相关 ====================
    
    def get_active_jobs(self, db: Session) -> List[ScheduledJob]:
        """获取所有激活状态的任务"""
        return db.query(ScheduledJob).filter(
            and_(
                ScheduledJob.job_status == JobStatus.ACTIVE,
                ScheduledJob.is_active == True
            )
        ).all()
    
    def get_jobs_by_group(self, db: Session, job_group: str) -> List[ScheduledJob]:
        """根据分组获取任务"""
        return db.query(ScheduledJob).filter(ScheduledJob.job_group == job_group).all()
    
    # ==================== 任务日志操作 ====================
    
    def create_job_log(self, db: Session, log_data: JobLogCreate) -> JobLog:
        """创建任务执行日志"""
        log_dict = log_data.model_dump()
        db_log = JobLog(**log_dict)
        db.add(db_log)
        db.commit()
        db.refresh(db_log)
        return db_log
    
    def get_job_log(self, db: Session, log_id: UUID) -> Optional[JobLog]:
        """获取单个任务日志"""
        return db.query(JobLog).filter(JobLog.id == log_id).first()
    
    def get_job_logs(
        self, 
        db: Session, 
        job_id: Optional[UUID] = None,
        skip: int = 0, 
        limit: int = 100,
        status: Optional[str] = None,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> tuple[List[JobLog], int]:
        """获取任务日志列表"""
        query = db.query(JobLog)
        
        # 添加过滤条件
        if job_id:
            query = query.filter(JobLog.job_id == job_id)
        if status:
            query = query.filter(JobLog.status == status)
        if start_date:
            query = query.filter(func.date(JobLog.trigger_time) >= start_date)
        if end_date:
            query = query.filter(func.date(JobLog.trigger_time) <= end_date)
        
        # 获取总数
        total = query.count()
        
        # 分页查询
        logs = query.order_by(desc(JobLog.trigger_time)).offset(skip).limit(limit).all()
        
        return logs, total
    
    def update_job_log(self, db: Session, log_id: UUID, log_data: JobLogUpdate) -> Optional[JobLog]:
        """更新任务日志"""
        log = self.get_job_log(db, log_id)
        if not log:
            return None
        
        update_dict = log_data.model_dump(exclude_unset=True)
        
        for field, value in update_dict.items():
            setattr(log, field, value)
        
        db.commit()
        db.refresh(log)
        return log
    
    def complete_job_execution(
        self, 
        db: Session, 
        log_id: UUID, 
        status: "ExecutionStatus",
        result_code: int = 0,
        result_msg: Optional[str] = None,
        process_log: Optional[str] = None,
        error_log: Optional[str] = None
    ) -> Optional[JobLog]:
        """完成任务执行，更新日志"""
        log = self.get_job_log(db, log_id)
        if not log:
            return None
        
        log.end_time = datetime.now()  # type: ignore
        log.status = str(status)  # type: ignore
        log.result_code = result_code  # type: ignore
        log.result_msg = result_msg  # type: ignore
        if process_log:
            log.process_log = process_log  # type: ignore
        if error_log:
            log.error_log = error_log  # type: ignore
        
        db.commit()
        db.refresh(log)
        return log
    
    # ==================== 任务分组操作 ====================
    
    def create_job_group(self, db: Session, group_data: JobGroupCreate) -> JobGroup:
        """创建任务分组"""
        group_dict = group_data.model_dump()
        db_group = JobGroup(**group_dict)
        db.add(db_group)
        db.commit()
        db.refresh(db_group)
        return db_group
    
    def get_job_group(self, db: Session, group_id: UUID) -> Optional[JobGroup]:
        """获取单个任务分组"""
        return db.query(JobGroup).filter(JobGroup.id == group_id).first()
    
    def get_job_group_by_name(self, db: Session, group_name: str) -> Optional[JobGroup]:
        """根据分组名称获取分组"""
        return db.query(JobGroup).filter(JobGroup.group_name == group_name).first()
    
    def get_job_groups(self, db: Session, skip: int = 0, limit: int = 100) -> tuple[List[JobGroup], int]:
        """获取任务分组列表"""
        query = db.query(JobGroup)
        total = query.count()
        groups = query.order_by(desc(JobGroup.created_at)).offset(skip).limit(limit).all()
        return groups, total
    
    def update_job_group(self, db: Session, group_id: UUID, group_data: JobGroupUpdate) -> Optional[JobGroup]:
        """更新任务分组"""
        group = self.get_job_group(db, group_id)
        if not group:
            return None
        
        update_dict = group_data.model_dump(exclude_unset=True)
        update_dict['updated_at'] = datetime.now()
        
        for field, value in update_dict.items():
            setattr(group, field, value)
        
        db.commit()
        db.refresh(group)
        return group
    
    def delete_job_group(self, db: Session, group_id: UUID) -> bool:
        """删除任务分组"""
        group = self.get_job_group(db, group_id)
        if not group:
            return False
        
        # 检查是否有任务使用该分组
        job_count = db.query(ScheduledJob).filter(ScheduledJob.job_group == group.group_name).count()
        if job_count > 0:
            return False  # 有任务使用该分组，不能删除
        
        db.delete(group)
        db.commit()
        return True
    
    # ==================== 统计相关操作 ====================
    
    def get_job_statistics(self, db: Session) -> Dict[str, Any]:
        """获取任务统计信息"""
        today = date.today()
        
        # 总任务数
        total_jobs = db.query(ScheduledJob).count()
        
        # 活跃任务数
        active_jobs = db.query(ScheduledJob).filter(ScheduledJob.job_status == JobStatus.ACTIVE).count()
        
        # 运行中任务数
        running_jobs = db.query(ScheduledJob).filter(ScheduledJob.job_status == JobStatus.RUNNING).count()
        
        # 今日执行统计
        today_logs = db.query(JobLog).filter(func.date(JobLog.trigger_time) == today)
        
        failed_jobs_today = today_logs.filter(JobLog.status == ExecutionStatus.FAILED).count()
        success_jobs_today = today_logs.filter(JobLog.status == ExecutionStatus.SUCCESS).count()
        total_executions_today = today_logs.count()
        
        return {
            "total_jobs": total_jobs,
            "active_jobs": active_jobs,
            "running_jobs": running_jobs,
            "failed_jobs_today": failed_jobs_today,
            "success_jobs_today": success_jobs_today,
            "total_executions_today": total_executions_today
        }
    
    def get_execution_trend(self, db: Session, days: int = 7) -> List[Dict[str, Any]]:
        """获取任务执行趋势"""
        end_date = date.today()
        start_date = end_date - timedelta(days=days-1)
        
        # 使用原生SQL查询，按日期分组统计
        sql = text("""
            SELECT 
                DATE(trigger_time) as date,
                SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) as success_count,
                SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) as failed_count,
                COUNT(*) as total_count
            FROM job_logs 
            WHERE DATE(trigger_time) BETWEEN :start_date AND :end_date
            GROUP BY DATE(trigger_time)
            ORDER BY DATE(trigger_time)
        """)
        
        result = db.execute(sql, {"start_date": start_date, "end_date": end_date}).fetchall()
        
        # 构建完整的日期序列
        trend_data = []
        current_date = start_date
        result_dict = {row.date: row for row in result}
        
        while current_date <= end_date:
            row = result_dict.get(current_date)
            trend_data.append({
                "date": current_date.strftime("%Y-%m-%d"),
                "success_count": row.success_count if row else 0,
                "failed_count": row.failed_count if row else 0,
                "total_count": row.total_count if row else 0
            })
            current_date += timedelta(days=1)
        
        return trend_data